package com.zzdz.jdbc.common;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.zzdz.jdbc.common.exception.CustomRuntimeException;
import com.zzdz.jdbc.common.exception.enums.ExceptionCodeEnum;
import com.zzdz.jdbc.common.log4j.LoggerUtils;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * JavaDataBaseConnectivityConnectionUtil<br>
 * JDBC 工具类
 * </p>
 *
 * @author XinLau
 * @version 1.0
 * @since 2019年10月16日 14:03
 */
public class JavaDataBaseConnectivityConnectionUtil {

    // JavaDataBaseConnectivityConnectionFactory - JDBC 连接工厂

    /**
     * executeStructuredQueryLanguageByPreparedStatement - 执行SQL语句
     *
     * @param structuredQueryLanguage - SQL语句
     * @param paramList               - SQL参数
     */
    public static void executeStructuredQueryLanguageByPreparedStatement(String structuredQueryLanguage, List<Object> paramList) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 建立连接
            connection = JavaDataBaseConnectivityConnectionFactory.getConnection();
            // 设定取消自动提交
            connection.setAutoCommit(false);
            // 创建预编译语句对象
            preparedStatement = connection.prepareStatement(structuredQueryLanguage);
            // 设置参数
            preparedStatement = doSetPreparedStatement(preparedStatement, paramList);
            // SQL语句
            doOutStructuredQueryLanguage(preparedStatement, structuredQueryLanguage);
            // 执行
            int rowNumber = preparedStatement.executeUpdate();
            LoggerUtils.debug(JavaDataBaseConnectivityConnectionUtil.class, "影响" + rowNumber + "行");
            // 提交
            JavaDataBaseConnectivityConnectionFactory.doCommit(connection);
            // 关闭连接
            JavaDataBaseConnectivityConnectionFactory.doClose(connection, preparedStatement);
        } catch (Exception e) {
            // 回滚
            JavaDataBaseConnectivityConnectionFactory.doRollback(connection);
            throw new CustomRuntimeException(ExceptionCodeEnum.JDBC_FUNCTION_EXCEPTION, e);
        }
    }

    /**
     * executeBatchStructuredQueryLanguageByPreparedStatement - 批量执行SQL语句
     *
     * @param structuredQueryLanguage - SQL语句
     * @param paramList               - SQL参数集
     */
    public static void executeBatchStructuredQueryLanguageByPreparedStatement(String structuredQueryLanguage, List<List<Object>> paramList) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 建立连接
            connection = JavaDataBaseConnectivityConnectionFactory.getConnection();
            // 设定取消自动提交
            connection.setAutoCommit(false);
            // 创建预编译语句对象
            preparedStatement = connection.prepareStatement(structuredQueryLanguage);
            // 设置参数
            for (List<Object> objectList : paramList) {
                preparedStatement = doSetPreparedStatement(preparedStatement, objectList);
                // SQL语句
                doOutStructuredQueryLanguage(preparedStatement, structuredQueryLanguage);
                preparedStatement.addBatch();
            }
            // 执行
            int[] rowNumber = preparedStatement.executeBatch();
            LoggerUtils.debug(JavaDataBaseConnectivityConnectionUtil.class, "影响" + rowNumber.length + "行");
            // 提交
            JavaDataBaseConnectivityConnectionFactory.doCommit(connection);
            // 关闭连接
            JavaDataBaseConnectivityConnectionFactory.doClose(connection, preparedStatement);
        } catch (Exception e) {
            // 回滚
            JavaDataBaseConnectivityConnectionFactory.doRollback(connection);
            throw new CustomRuntimeException(ExceptionCodeEnum.JDBC_FUNCTION_EXCEPTION, e);
        }
    }

    /**
     * executeQueryByPreparedStatement - 执行查询SQL语句
     *
     * @param structuredQueryLanguage - SQL语句
     * @param paramList               - SQL参数
     */
    public static List<Map<Object, Object>> executeQueryByPreparedStatement(String structuredQueryLanguage, List<Object> paramList) {
        List<Map<Object, Object>> mapList = new ArrayList<Map<Object, Object>>(16);
        try {
            // 建立连接
            Connection connection = JavaDataBaseConnectivityConnectionFactory.getConnection();
            // 设置只读
            connection.setReadOnly(true);
            // 创建预编译语句对象
            PreparedStatement preparedStatement = connection.prepareStatement(structuredQueryLanguage, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            // 设置参数
            preparedStatement = doSetPreparedStatement(preparedStatement, paramList);
            // SQL语句
            doOutStructuredQueryLanguage(preparedStatement, structuredQueryLanguage);
            // 执行查询
            ResultSet resultSet = preparedStatement.executeQuery();
            // 处理返回值
            mapList = doResultSetToList(resultSet);
            // 关闭连接
            JavaDataBaseConnectivityConnectionFactory.doClose(connection, preparedStatement);
        } catch (Exception e) {
            throw new CustomRuntimeException(ExceptionCodeEnum.JDBC_FUNCTION_EXCEPTION, e);
        }
        return mapList;
    }

    /**
     * mapList转beanList
     * @param mapList - mapList
     * @param beanClass - bean
     * @param <T>
     * @return
     */
    public static <T> List<T> mapListToBeanList(List<Map<Object, Object>> mapList, Class<T> beanClass) {
        List<T> beanList = new ArrayList();
        if (mapList.size() > 0 && beanClass != null) {
            for (Map<Object, Object> map : mapList) {
                beanList.add(JSON.parseObject(mapToJSONString(map), beanClass));
            }
        }
        return beanList;
    }

    /**
     * map转bean
     * @param map - map
     * @param beanClass - bean
     * @param <T>
     * @return bean
     */
    public static <T> T mapToBean(Map<Object, Object> map, Class<T> beanClass) {
        if (map == null && beanClass != null) {
            return null;
        } else {
            return JSON.parseObject(mapToJSONString(map), beanClass);
        }
    }

    /**
     * map转JSONString
     * @param map - map
     * @return JSONString
     */
    public static String mapToJSONString(Map<Object, Object> map){
        return JSON.toJSONString(map);
    }

    /**
     * mapList转JSONString
     * @param mapList - mapList
     * @return JSONString
     */
    public static String mapListToJSONString(List<Map<Object, Object>> mapList){
        return JSON.toJSONString(mapList);
    }

    /**
     * 利用反射获取类的属性信息
     * @param object - 对象
     * @return  Map<String, Object>
     */
    public static Map<Object, Object> doReflectionForAnalysisbBean(Object object){
        Map<Object, Object> map = new HashMap<Object, Object>(16);
        // 对象非空判断
        if (object != null && !object.equals("") && !object.equals("null")){
            // 获得类
            Class clazz = object.getClass();
            // 获取实体类的所有属性信息，返回Field数组
            Field[] declaredFields = clazz.getDeclaredFields();
            // 属性的类型
            JSONArray genericType = new JSONArray();
            // 属性名
            JSONArray genericName = new JSONArray();
            // 属性修饰符
            JSONArray genericModifiers = new JSONArray();
            for (Field field : declaredFields) {
                genericType.add(field.getGenericType().toString());
                genericName.add(field.getName());
                genericModifiers.add(field.getModifiers());
                System.out.println(field.toString());
            }
//            map.put("genericType", genericType);
//            map.put("genericName", genericName);
//            map.put("genericModifiers", genericModifiers);
            map.put("declaredFields", declaredFields);
        }
        return  map;
    }

    /**
     * ResultSet 转成 ArrayList
     *
     * @param resultSet
     * @return List<Map < String, Object>>
     * @throws SQLException
     */
    private static List<Map<Object, Object>> doResultSetToList(ResultSet resultSet) throws SQLException {
        /**
         * 初始化返回值大小
         */
        resultSet.last();
        int size = resultSet.getRow();
        LoggerUtils.debug(JavaDataBaseConnectivityConnectionUtil.class, " 共查询到：" + size + "条记录");
        List<Map<Object, Object>> resultArrayList = new ArrayList<Map<Object, Object>>(size);
        resultSet.beforeFirst();
        /**
         * 处理列名，生成列明数组
         */
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        int columnCount = resultSetMetaData.getColumnCount();
        List<String> columnNameList = new ArrayList<String>(columnCount);
        for (int i = 0; i < columnCount; i++) {
            columnNameList.add(resultSetMetaData.getColumnName(i + 1));
        }
        /**
         * 解析数据
         */
        while (resultSet.next()) {
            // 获取当前操作的数据行数
            int row = resultSet.getRow();
            // 单行数据Map
            Map<Object, Object> map = new HashMap<Object, Object>(columnNameList.size());
            for (String key : columnNameList) {
                Object value = resultSet.getString(key);
                if (value == null) {
                    // 对空对象处理，防止丢失
                    value = "";
                }
                map.put(key, value);
            }
            resultArrayList.add(map);
        }
        return resultArrayList;
    }

    /**
     * 设置查询参数
     *
     * @param preparedStatement
     * @param paramList
     * @return PreparedStatement
     * @throws SQLException
     */
    private static PreparedStatement doSetPreparedStatement(PreparedStatement preparedStatement, List<Object> paramList) throws SQLException {
        if (paramList.size() > 1) {
            for (int key = 0; key < paramList.size(); key++) {
                Object paramObject = paramList.get(key);
                if (paramObject instanceof Integer) {
                    int intValue = ((Integer) paramObject).intValue();
                    preparedStatement.setInt(key + 1, intValue);
                } else if (paramObject instanceof Byte) {
                    Byte byteValue = (Byte) paramObject;
                    preparedStatement.setByte(key + 1, byteValue);
                } else if (paramObject instanceof String) {
                    String stringValue = (String) paramObject;
                    preparedStatement.setString(key + 1, stringValue);
                } else if (paramObject instanceof Double) {
                    double doubleValue = ((Double) paramObject).doubleValue();
                    preparedStatement.setDouble(key + 1, doubleValue);
                } else if (paramObject instanceof Float) {
                    float floatValue = ((Float) paramObject).floatValue();
                    preparedStatement.setFloat(key + 1, floatValue);
                } else if (paramObject instanceof Long) {
                    long longValue = ((Long) paramObject).longValue();
                    preparedStatement.setLong(key + 1, longValue);
                } else if (paramObject instanceof Boolean) {
                    boolean booleanValue = ((Boolean) paramObject).booleanValue();
                    preparedStatement.setBoolean(key + 1, booleanValue);
                } else if (paramObject instanceof java.util.Date) {
                    java.sql.Date sqlDateValue = (java.sql.Date) paramObject;
                    preparedStatement.setDate(key + 1, sqlDateValue);
                } else if (paramObject.getClass().isArray()) {
                    // 反射 获得类型
                    String stringValue = JSON.toJSONString(paramObject);
                    stringValue = stringValue.replace("[", "(");
                    stringValue = stringValue.replace("]", ")");
                    preparedStatement.setString(key + 1, stringValue);
                } else if (paramObject instanceof BigDecimal) {
                    BigDecimal bigDecimalValue = (BigDecimal) paramObject;
                    preparedStatement.setBigDecimal(key + 1, bigDecimalValue);
                } else {
                    preparedStatement.setObject(key + 1, paramObject);
                }
            }
        }
        return preparedStatement;
    }

    /**
     * Log4j输出SQL语句
     * @param preparedStatement - PreparedStatement
     * @param structuredQueryLanguage - SQL语句
     */
    private static String doOutStructuredQueryLanguage(PreparedStatement preparedStatement, String structuredQueryLanguage){
        if (preparedStatement instanceof com.mysql.jdbc.PreparedStatement){
            // MySQL
            com.mysql.jdbc.PreparedStatement mySqlPreparedStatement = (com.mysql.jdbc.PreparedStatement)preparedStatement;
            String preparedStatementSQL = mySqlPreparedStatement.toString();
            structuredQueryLanguage = preparedStatementSQL.substring(preparedStatementSQL.lastIndexOf(":") + 1);
        }else if (preparedStatement instanceof oracle.jdbc.driver.OraclePreparedStatement){
            // Oracle
            oracle.jdbc.driver.OraclePreparedStatement oraclePreparedStatement = (oracle.jdbc.driver.OraclePreparedStatement)preparedStatement;
            try {
                structuredQueryLanguage = oraclePreparedStatement.getOriginalSql();
            } catch (SQLException e) {
                LoggerUtils.error(JavaDataBaseConnectivityConnectionUtil.class, e.getMessage());
            }
        }
        LoggerUtils.debug(JavaDataBaseConnectivityConnectionUtil.class, structuredQueryLanguage);
        return structuredQueryLanguage;
    }

}
